/**************************************************************************
	Replication do-file: "Corruption in Customs"
	Cyril Chalendard, Ana Fernandes, Gael Raballand and Bob Rijkers
	
	Created on: 01/07/2022
**************************************************************************/

clear all
set more off, perm
cap log close
cls

* ----------------------- DIRECTORIES AND FOLDERS ----------------------- *

* Directories in which data are stored
global main "PUT YOUR DIRECTORY PATH HERE"
cd "$folder"

* Folders
global outputdata = "$main\Output Data"
cap mkdir "$main\Tables"
global tables = "$main\Tables"

* ----------------------------- BEGINS HERE ----------------------------- *

* -------
* Dataset
* -------
use "$outputdata\CFRR.dta", clear // open dataset

* Sample
keep if sample_int==1 & eis_f!=. // keep observations of interest


* ---------
* Table A20
* ---------

* Matrix
forvalues i = 1/2 {
	mat tableA20_`i' = J(4,3,.)
}
mat tableA20_3 = J(1,1,.)

* Percentage of all declarations
** Declarations with and without initial excess interaction
qui: tab sig_c_i if sample_reg==1 & sig_c_f!=. & reass!=. , matcell(mat1)
mat tableA20_1[1,1] = mat1[1,1]/r(N)
mat tableA20_2[1,1] = mat1[2,1]/r(N)
** Not reassigned
qui: tab reass sig_c_i if sample_reg==1 & sig_c_f!=. , matcell(mat2)
mat tableA20_1[2,1] = mat2[1,1]/r(N)
mat tableA20_2[2,1] = mat2[1,2]/r(N)
** Reassigned to inspector with or without excess interaction
qui: tab to_no_excess sig_c_i, matcell(mat3)
mat tableA20_1[3,1] = mat3[2,1]/r(N)
mat tableA20_2[3,1] = mat3[2,2]/r(N)
** Reassigned to inspector with or without excess interaction
qui: tab to_excess sig_c_i, matcell(mat4)
mat tableA20_1[4,1] = mat4[2,1]/r(N)
mat tableA20_2[4,1] = mat4[2,2]/r(N)
** Any re-assignment
qui: tab reass if sample_reg==1, matcell(mat5)
mat tableA20_3[1,1] = mat5[2,1]/r(N)

* Percentage of all re-assignments
** Without initial excess to no excess
qui: tab rLL if sample_reg==1 & sig_c_f!=. & reass==1, matcell(mat6)
mat tableA20_1[3,2] = mat6[2,1]/r(N)
** Without initial excess to excess
qui: tab rLH if sample_reg==1 & sig_c_f!=. & reass==1, matcell(mat7)
mat tableA20_1[4,2] = mat7[2,1]/r(N)
** With initial excess to no excess
qui: tab rHL if sample_reg==1 & sig_c_f!=. & reass==1, matcell(mat8)
mat tableA20_2[3,2] = mat8[2,1]/r(N)
** With initial excess to excess
qui: tab rHH if sample_reg==1 & sig_c_f!=. & reass==1, matcell(mat9)
mat tableA20_2[4,2] = mat9[2,1]/r(N)

* Probability of re-assignment conditional on initial state
forvalues i = 1/2 {
	mat tableA20_`i'[3,3] = tableA20_`i'[3,1]/tableA20_`i'[1,1]
	mat tableA20_`i'[4,3] = tableA20_`i'[4,1]/tableA20_`i'[1,1]
}


* Export
* ------
putexcel set "$tables\Table A20.xlsx", replace sheet(tableA20) // create a new excel spreadsheet

* Main titles
putexcel B1 = "% of all declarations", hcenter vcenter bold
putexcel C1 = "% of all re-assignments", hcenter vcenter bold
putexcel D1 = "probability of re-assignment conditional on initial state", hcenter vcenter bold

* Row names
local j = 3
local names `" "Declarations without initial excess interaction" "Not re-assigned" "Re-assigned to inspector without excess interaction (RNN)" "Re-assigned to inspector with excess interaction (RNE)" "'
forvalues i = 1(1)4 {
	
	local x: word `i' of `names'
	
	putexcel A`j' = "`x'", left vcenter
	local j = `j'+1
}
local j = 8
local names `" "Declarations with initial excess interaction" "Not re-assigned" "Re-assigned to inspector without excess interaction (REN)" "Re-assigned to inspector with excess interaction (REE)" "'
forvalues i = 1(1)4 {
	
	local x: word `i' of `names'
	
	putexcel A`j' = "`x'", left vcenter
	local j = `j'+1
}
putexcel A13 = "Any re-assignment", left vcenter

* Coefficients
putexcel B3 = matrix(tableA20_1), nformat(0.0%) hcenter vcenter
putexcel B8 = matrix(tableA20_2), nformat(0.0%) hcenter vcenter
putexcel B13 = matrix(tableA20_3), nformat(0.0%) hcenter vcenter


* -------------------------------- ENDS HERE -------------------------------- *